{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "<img src=\"https://github.com/chdb-io/chdb/raw/main/docs/_static/snake-chdb.png\" width=150>\n",
        "\n",
        "# chdb + urleng\n",
        "This example shows the combined power of chdb and urleng to persist sessions on the cloud\n",
        "\n",
        "* [chdb](https://chdb.io) is an in-memory OLAP database powered by ClickHouse\n",
        "* [urleng.com](https://urleng.com) is a free pastie service for ClickHouse url tables"
      ],
      "metadata": {
        "id": "olco61H9GcX2"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "W56ggkPiD9ZB"
      },
      "outputs": [],
      "source": [
        "!pip install chdb --upgrade --quiet"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import uuid\n",
        "from chdb import session as chs\n",
        "\n",
        "## Generate a secret, unique secret urleng.com session token & save for later\n",
        "## url_token = str(uuid.uuid1())\n",
        "url_token = 'dc81bb0a-4902-11ee-a84b-0242ac1c000c' # just for demo purposes\n",
        "%store url_token\n",
        "\n",
        "print(url_token)\n",
        "\n",
        "## Create DB, Table, View in temp session, auto cleanup when session is deleted.\n",
        "sess = chs.Session()\n",
        "\n",
        "sess.query(\"CREATE DATABASE IF NOT EXISTS db_xxx ENGINE = Atomic\")\n",
        "sess.query(\"CREATE TABLE IF NOT EXISTS db_xxx.log_table_xxx (key String, value UInt64) ENGINE = Log;\")\n",
        "sess.query(\"INSERT INTO db_xxx.log_table_xxx VALUES ('a', 1), ('b', 3), ('c', 2), ('d', 5);\")\n",
        "\n",
        "print(\"Select from session:\\n\")\n",
        "print(sess.query(\"SELECT * FROM db_xxx.log_table_xxx\", \"Pretty\"))\n",
        "\n",
        "## Copy to urlengine table, persist the session on the cloud.\n",
        "sess.query(\"INSERT INTO FUNCTION url('https://urleng.com/\"+url_token+\"', JSONEachRow, 'key String, value UInt64') SELECT * FROM db_xxx.log_table_xxx\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d41c860e-503a-4203-a259-9a7b2864050f",
        "id": "9mDQLI3NFVQP"
      },
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Stored 'url_token' (str)\n",
            "dc81bb0a-4902-11ee-a84b-0242ac1c000c\n",
            "Select from session:\n",
            "\n",
            "┏━━━━━┳━━━━━━━┓\n",
            "┃ \u001b[1mkey\u001b[0m ┃ \u001b[1mvalue\u001b[0m ┃\n",
            "┡━━━━━╇━━━━━━━┩\n",
            "│ a   │     1 │\n",
            "├─────┼───────┤\n",
            "│ b   │     3 │\n",
            "├─────┼───────┤\n",
            "│ c   │     2 │\n",
            "├─────┼───────┤\n",
            "│ d   │     5 │\n",
            "└─────┴───────┘\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": []
          },
          "metadata": {},
          "execution_count": 41
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from chdb import session as chs\n",
        "\n",
        "## Create a new DB, Table, View in temp session, auto cleanup when session is deleted.\n",
        "new_sess = chs.Session()\n",
        "\n",
        "## Retrieve the urlengine session token\n",
        "%store -r url_token\n",
        "print(url_token)\n",
        "\n",
        "new_sess.query(\"CREATE DATABASE IF NOT EXISTS db_xxx ENGINE = Atomic\")\n",
        "new_sess.query(\"CREATE TABLE IF NOT EXISTS db_xxx.log_table_xxx (key String, value UInt64) ENGINE = Log;\")\n",
        "\n",
        "## There's no data in our session yet. Zero results.\n",
        "print(new_sess.query(\"SELECT * FROM db_xxx.log_table_xxx\", \"Pretty\"))\n",
        "\n",
        "## Restore session from the cloud, insert data from urleng table\n",
        "new_sess.query(\"INSERT INTO db_xxx.log_table_xxx SELECT * FROM url('https://urleng.com//\"+url_token+\"', JSONEachRow)\")\n",
        "\n",
        "print(\"Select from restored session:\\n\")\n",
        "print(new_sess.query(\"SELECT * FROM db_xxx.log_table_xxx\", \"Pretty\"))\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e9c254b1-6c71-4545-f67c-9c7d5a556031",
        "id": "xW9B4jryHNIr"
      },
      "execution_count": 42,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "dc81bb0a-4902-11ee-a84b-0242ac1c000c\n",
            "\n",
            "Select from restored view:\n",
            "\n",
            "┏━━━━━┳━━━━━━━┓\n",
            "┃ \u001b[1mkey\u001b[0m ┃ \u001b[1mvalue\u001b[0m ┃\n",
            "┡━━━━━╇━━━━━━━┩\n",
            "│ a   │     1 │\n",
            "├─────┼───────┤\n",
            "│ b   │     3 │\n",
            "├─────┼───────┤\n",
            "│ c   │     2 │\n",
            "├─────┼───────┤\n",
            "│ d   │     5 │\n",
            "└─────┴───────┘\n",
            "\n"
          ]
        }
      ]
    }
  ]
}
